# -- coding: utf-8 -- 
# @Author:  Kaixiang Song
# @Project: 数据结构与算法 
# @File:    combine_wx_zfb.py
# @Time:    2020/7/6 13:51 

import os
import sys

# #先把pip升级到最新版本
# path = '"'+os.path.dirname(sys.executable)+'\\pip" install --upgrade pip'
# os.system(path)

#导入必要的扩展库
#pandas扩展库
try:
    import pandas as pd
except:
    path = '"'+os.path.dirname(sys.executable)+'\\pip" install pandas'
    os.system(path)
    import pandas as pd

from helper import print_data_info, get_data, update_data_types

def read(path_csv, rules_list, save_path, start='2020/6/1 0:0:0', end='2020/7/1 0:0:0'):
    ys,  hb, dt = list(zip(*rules_list))
    data1 = get_data(path_csv)
    # 更新数据格式。
    data1 = update_data_types(data1, {k: v for k, v in zip(ys, dt)})
    data1 = data1.sort_values(by='交易创建时间')              # 按照交易时间排序
    data1 = data1.loc[(data1['交易创建时间'] > start) & (data1['交易创建时间'] < end)]  # 筛选出满足时间要求的记录
    print_data_info(data1, '合并后账单')
    print('\n{}至{}期间数据统计：'.format(start, end))
    data_analysis(data1)
    # str_start=datetime.datetime.strptime(start,'%Y-%m-%d %H:%M:%S').date()
    # str_end = end.format("yyyymmdd")
    # save_path = save_path + '{}至{}期间数据.xlsx'.format(str_start,str_end) 
    data1.to_excel(save_path, encoding='gbk')
    # return data1

# def read(path_csv, rules_list, save_path, start='2020/6/1 0:0:0', end='2020/7/1 0:0:0'):
#     wx, zfb, hb, dt = list(zip(*rules_list))

#     # data_wx = get_data(path_wx, start_line=16, encoding='utf-8')
#     # data_zfb = get_data(path_zfb, start_line=4, encoding='gbk')
#     data_wx = get_data(path_wx, start_line=0, encoding='utf-8')
#     data_zfb = get_data(path_zfb, start_line=0, encoding='utf-8')

#     # data_wx = transaction_checking_wx(data_wx, {k: v for k, v in zip(wx, dt)})
#     # data_zfb = transaction_checking_zfb(data_zfb, {k: v for k, v in zip(zfb, dt)})

#     # data_wx = extract_and_rename(data_wx, hb, {k: v for k, v in zip(wx, hb)})
#     # data_zfb = extract_and_rename(data_zfb, hb, {k: v for k, v in zip(zfb, hb)})

#     data = pd.concat([data_wx, data_zfb], axis=0, sort=False)       # 合并
#     data = data.sort_values(by='交易时间')              # 按照交易时间排序
#     data = data.loc[(data['交易时间'] > start) & (data['交易时间'] < end)]  # 筛选出满足时间要求的记录
#     print_data_info(data, '合并后账单')

#     print('\n{}至{}期间数据统计：'.format(start, end))
#     data_analysis(data)

#     data.to_excel(save_path, encoding='gbk')

# #
# # def post_process(data):
# #     """
# #     对合并后的数据做一些后处理。
# #     :param data:
# #     :return:
# #     """
# #     return data


def data_analysis(data):
    print('总收入：{:.2f}元。'.format(sum(data.loc[data['收支类型'] == '收入', ['收入']].values)[0]))
    print('总支出：{:.2f}元。'.format(sum(data.loc[data['收支类型'] == '支出', ['支出']].values)[0]))


if __name__ == "__main__":
    path_csv = r'E:\GiteeWarseHouse\账本\网银流水合集_UTF8.csv'
    save_path= r'E:\GiteeWarseHouse\账本\网银流水合集.xlsx'

    #               更改列名前          更改列名后          列数据类型
    rules_list = [['交易号',            '交易号',           'object'],
                    ['商家订单号',      '商家订单号',       'object'],
                    ['交易创建时间',    '交易创建时间',      'datetime64'],
                    ['付款时间',        '付款时间',         'datetime64'],
                    ['最近修改时间',    '最近修改时间',     'datetime64'],
                    ['交易行',          '交易行',           'object'],
                    ['交易类型',        '交易类型',         'object'],
                    ['对方户名.1',      '对方户名.1',       'object'],
                    ['对方户名.2',      '对方户名.2',       'object'],
                    ['对方户名.3',      '对方户名.3',       'object'],
                    ['交易摘要',        '交易摘要',         'object'],
                    ['收入',            '收入',             'float64'],
                    ['支出',            '支出',             'float64'],
                    ['结余',            '结余',             'float64'],
                    ['收支类型',        '收支类型',         'object'],
                    ['ID',              'ID',               'int64'],
                    ['账户',            '账户',             'object'],
                    ['对方账号',        '对方账号',         'object'],
                    ['交易日期',        '交易日期',         'datetime64'],
                    ['交易时间',        '交易时间',         'datetime64'],
                    ['交易渠道',        '交易渠道',         'object'],
                    ['交易用途',        '交易用途',         'object'],
                    ['时间戳',          '时间戳',           'object'],
                    ['导入时间',        '导入时间',         'datetime64'],
                    ['支付宝交易号',    '支付宝交易号',     'object'],
                    ['数据来源',        '数据来源',         'object'],
                    ['人员',            '人员',             'object'],
                    ['更新时间',        '更新时间',         'datetime64'],
                    ['服务费',          '服务费',           'float64'],
                    ['退款金额',        '退款金额',         'float64'],
                    ['备注',            '备注',             'object'],
                    ['资金状态',        '资金状态',         'object'],
                    ['交易状态',        '交易状态',         'object'],
                    ['交易来源地',      '交易来源地',       'object'],
                    ['对方户名',        '对方户名',         'object'],
                  ]

    data = read(path_csv, rules_list,save_path,
          start='2020/9/1 0:0:0', end='2020/10/1 0:0:0')

    #  content = byte_content.decode('gbk')
    #     lines = content.split("\n")
    #     if (lines[0] != '支付宝交易记录明细查询\r'):
    #         raise 'Not Alipay Trade Record!'
    #     print('Import Alipay: ' + lines[2])
    #     content = "\n".join(lines[4:len(lines) - 8])
    #     self.content = content
    #     self.deduplicate = Deduplicate(entries, option_map)

    #     content = self.content
    #     f = StringIO(content)
    #     reader = DictReaderStrip(f, delimiter=',')
    #     transactions = []
    #     i1=0

    # transactions = []
    # i1=0
    # for row in data:
    #     i1 = i1 + 1
    #     if row['交易状态'] == '交易关闭' and row['资金状态'] == '':
    #         continue
    #     if row['交易状态'] == '冻结成功':
    #         continue
    #     time = row['付款时间']
    #     if time == '':
    #         time = row['交易创建时间']
    #     print("{} . Importing {} at {}".format(i1,row['商品名称'], time))
    #     # 开始生成参数
    #     meta = {}# 空字典
    #     time = dateparser.parse(time)                                               #获得交易付款或者创建时间
    #     meta['alipay_trade_no'] = row['交易号']                                     #记录支付宝交易号
    #     meta['trade_time'] = str(time)                                              #记录交易时间字符串
    #     meta['timestamp'] = str(time.timestamp()).replace('.0', '')                 #记录时间戳
    #     account = get_account_by_guess(row['交易对方'], row['商品名称'], time)       #查询科目 待细致检查工作原理
    #     flag = "*"                                                                 #复核标志默认为已确认
    #     amount = float(row['金额（元）'])#读取交易金额
    #     #如果账户是未知的账户 则复核标志改为未确认
    #     if account == "Expenses:Unknown":
    #         flag = "!"
    #     # 字典添加备注
    #     if row['备注'] != '':
    #         meta['note'] = row['备注']
    #     # 字典添加商家订单号
    #     if row['商家订单号'] != '':
    #         meta['shop_trade_no'] = row['商家订单号']

    #     meta = data.new_metadata(
    #         'beancount/core/testing.beancount',
    #         12345,
    #         meta
    #     )
    #     entry = Transaction(
    #         meta,
    #         date(time.year, time.month, time.day),
    #         flag,
    #         row['交易对方'],
    #         row['商品名称'],
    #         data.EMPTY_SET,
    #         data.EMPTY_SET, []
    #     )
    #     price = row['金额（元）']
    #     money_status = row['资金状态']
    #     if money_status == '已支出':
    #         data.create_simple_posting(entry, Account支付宝, None, None)
    #         amount = -amount
    #     elif money_status == '资金转移':
    #         data.create_simple_posting(entry, Account支付宝, None, None)
    #     elif money_status == '已收入':
    #         if row['交易状态'] == '退款成功':
    #             # 收钱码收款时，退款成功时资金状态为已支出
    #             price = '-' + price
    #             data.create_simple_posting(entry, Account支付宝, None, None)
    #         else:
    #             income = get_income_account_by_guess(
    #                 row['交易对方'], row['商品名称'], time)
    #             if income == 'Income:Unknown':
    #                 entry = entry._replace(flag='!')
    #             data.create_simple_posting(entry, income, None, None)
    #             if flag == "!":
    #                 account = Account支付宝
    #     else:
    #         print('Unknown status')
    #         print(row)

    #     data.create_simple_posting(entry, account, price, 'CNY')
    #     if (row['服务费（元）'] != '0.00'):
    #         data.create_simple_posting(
    #             entry, 'Expenses:Fee', row['服务费（元）'], 'CNY')

    #     #b = printer.format_entry(entry)
    #     # print(b)
    #     if not self.deduplicate.find_duplicate(entry, amount, 'alipay_trade_no'):
    #         transactions.append(entry)

    # b= transactions
    # self.deduplicate.apply_beans()
    # return transactions


#     path_csv = 'F:/jzb/data/wxPay.csv'
#     save_path = 'F:/jzb/data/last.xlsx'

#     #                微信          支付宝         合并后    数据类型
#     rules_list = [['交易时间', '交易创建时间', '交易时间', 'datetime64'],
#                   ['交易对方', '交易对方',     '交易对方', 'object'],
#                   ['商户单号', '商家订单号',   '商户单号', 'object'],
#                   ['交易类型', '类型',         '交易类型', 'object'],
#                   ['交易单号', '交易号',       '交易单号', 'object'],
#                   ['当前状态', '资金状态',     '当前状态', 'object'],
#                   ['商品',     '商品名称',     '商品名称', 'object'],
#                   ['收/支',    '收/支',        '收支',     'object'],
#                   ['金额(元)', '金额（元）',   '交易金额', 'float64'],
#                   ['备注',     '备注',         '备注',     'object'],
#                   ]

#     merge(path_wx,rules_list, save_path,
#           start='2020/9/1 0:0:0', end='2020/10/1 0:0:0')
